package com.bdqn.dao;

import com.bdqn.pojo.Newtable;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewDao {




    public static Newtable GetObj(String id) throws Exception {
        Newtable pojo=new Newtable();
        String sql="select * from newtable where id=?";
        ResultSet set=BaseDao.getResultSet(sql,new Object[]{id});
        if(set.next())
        {
            pojo.setId(set.getInt("id"));
            pojo.setTitle(set.getString("title"));
            pojo.setAuthor(set.getString("author"));
            pojo.setContext(set.getString("context"));
            pojo.setMark(set.getString(5));
            pojo.setCdate(set.getDate("cdate"));
            pojo.setNtype(set.getInt("ntype"));
        }
        return pojo;
    }


    public static void main(String[] args) throws Exception {
        sql(" '标题' or 1=1");
    }
    //sql 注入问题，黑客会根据系统中的漏洞去获取或者损害系统
    public static List<Newtable> sql(String title) throws Exception {
        List<Newtable> list=new ArrayList<>();
        //jdbc 读取数据
        //加载驱动 8版本使用com.mysql.cj.jdbc.Driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建连接串
        String url="jdbc:mysql://localhost:3306/newproject?useUnicode=true&characterEncoding=UTF-8";
        Connection connection= DriverManager.getConnection(url,"root","root");
        String sql="select * from newtable where title=?";
        PreparedStatement
                statement=connection.prepareStatement(sql);
        statement.setString(1,title);
        ResultSet set=statement.executeQuery();
        while (set.next())
        {
            System.out.println(set.getString("title"));
            Newtable pojo=new Newtable();
            pojo.setId(set.getInt("id"));
            pojo.setTitle(set.getString("title"));
            pojo.setAuthor(set.getString("author"));
            pojo.setContext(set.getString("context"));
            pojo.setMark(set.getString(5));
            pojo.setCdate(set.getDate("cdate"));
            /* pojo.setName(set.getString("name"));*/
            list.add(pojo);
        }
        set.close();
        statement.close();
        connection.close();
        return list;
    }

    //取出一共有多少页
    public static int count(String nn) throws Exception {
        int count=0;
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建连接串
        String url="jdbc:mysql://localhost:3306/newproject?useUnicode=true&characterEncoding=UTF-8";
        Connection connection= DriverManager.getConnection(url,"root","root");
        String sql="select count(id) from newtable where title like '%"+nn+"%'";
        Statement
                statement=connection.createStatement();
        ResultSet set=statement.executeQuery(sql);
        if(set.next())
        {
            count=set.getInt(1);
        }
        return count%3==0?count/3:count/3+1;
    }


    public static void update() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建连接串
        String url="jdbc:mysql://localhost:3306/newproject?useUnicode=true&characterEncoding=UTF-8";
        Connection connection= DriverManager.getConnection(url,"root","root");
        String sql="insert into newtable(title,ntype) value(?,?)";
        PreparedStatement
                statement=connection.prepareStatement(sql);
        statement.setString(0,"标题");
        statement.setInt(1,2);
        int set=statement.executeUpdate();
    }


    public static List<Newtable> getlist(int index,String nn) throws Exception {
        List<Newtable> list=new ArrayList<>();
        //jdbc 读取数据
        //加载驱动 8版本使用com.mysql.cj.jdbc.Driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建连接串
        String url="jdbc:mysql://localhost:3306/newproject?useUnicode=true&characterEncoding=UTF-8";
        Connection connection= DriverManager.getConnection(url,"root","root");
        String sql="select a.*,b.name from newtable a " +
                "inner join newType b " +
                "on a.ntype=b.id where title like ? order by id desc " +
                "limit ?,3 ";
        PreparedStatement
                 statement=connection.prepareStatement(sql);
        statement.setString(1,"%"+nn+"%");
        statement.setInt(2,(index-1)*3);
        ResultSet set=statement.executeQuery();
        while (set.next())
        {
            Newtable pojo=new Newtable();
            pojo.setId(set.getInt("id"));
            pojo.setTitle(set.getString("title"));
            pojo.setAuthor(set.getString("author"));
            pojo.setContext(set.getString("context"));
            pojo.setMark(set.getString(5));
            pojo.setCdate(set.getDate("cdate"));
            pojo.setName(set.getString("name"));
            list.add(pojo);
        }
        set.close();
        statement.close();
        connection.close();
        return list;
    }
}
